package com.sxfq.loansupermarket.backsystem.modular.refunds.service.impl;

import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.CheckoutMethodInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchPaymentTotalInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.IPaymentTotalService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class PaymentTotalServiceImpl implements IPaymentTotalService {
    @Autowired
    private SqlMapper sqlMapper;
    @Autowired
    private RedisTemplate redisTemplate;
    @Override
    public List<Map<String, Object>> getList(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {
        String tableHead = searchPaymentTotalInfo.getAbbreviation();
        String selectsql = "select t.id,t.checkout_method from  " + tableHead + "recharge_info"  + " t left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1";  //0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            selectsql += " and t.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            selectsql += " and t.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        selectsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(selectsql, CheckoutMethodInfo.class);
        String sql = "select * from(select a.customer_id as customerId, b.customer_name as '客户姓名',b.customer_phone as '电话'," +
                " b.customer_sex as '性别', b.customer_age as '年龄',a.recharge_time as '时间'," +
                " a.recharge_people as '收费人', b.customer_status as '客户状态', ifnull(a.tc_name,c.tc_name) as 'tc', ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " a.remarks as  '备注' from " + tableHead + "recharge_info" + " a left join " + tableHead + "customer_info" + " b on a.customer_id=b.id" +
                " left join " + tableHead + "deal_info" + " c on a.deal_id=c.id " +
                " where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id order by a.recharge_time desc) t union all  " +
                " select '' as customerId, '合计' as '客户姓名','' as '客户电话','' as '性别','' as '年龄','' as  '时间'," +
                " '' as '收费人',sum(t.actualAccountBalance) AS '客户状态','' as 'tc',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(t." + list1.get(i).getCheckoutMethod() + ") ,";
            }
        }
        sql += " '' as  '备注' from (select '' as customerId, '合计' as '客户姓名','' as '客户电话','' as '性别','' as '年龄','' as  '时间',  " +
                " '' as '收费人'," +
                " a.actual_account_balance as actualAccountBalance," +
                "'' as 'tc',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " '' as  '备注' from " + tableHead + "recharge_info" + " a left join " + tableHead + "customer_info" + " b on a.customer_id=b.id  " +
                " left join " + tableHead + "deal_info" + " c on a.deal_id=c.id where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchPaymentTotalInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id ) t";
        List<Map<String, Object>> list = sqlMapper.selectList(sql);
        return list;
    }

    @Override
    public List<LinkedHashMap<String, Object>> paymentTableExport(SearchPaymentTotalInfo searchPaymentTotalInfo, String reach) {
        String selectsql = "select t.id,t.checkout_method from  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t left join checkout_method_info t1 on t.checkout_method_id=t1.id where 1=1";  //0:正常方式，1:积分，2:优惠券 , 3:储值
        if (searchPaymentTotalInfo.getStartTime() != null && searchPaymentTotalInfo.getStartTime() != "") {
            selectsql += " and t.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "' ";
        }
        if (searchPaymentTotalInfo.getEndTime() != null && searchPaymentTotalInfo.getEndTime() != "") {
            selectsql += " and t.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "' ";
        }
        selectsql += " group by t.checkout_method order by t1.num";
        List<CheckoutMethodInfo> list1 = sqlMapper.selectList(selectsql, CheckoutMethodInfo.class);
        String sql = "select * from(select b.customer_name as '客户姓名',b.customer_phone as '电话'," +
                "( CASE b.customer_sex " +
                " WHEN 1 THEN " +
                " '男' " +
                " WHEN 2 THEN " +
                " '女' " +
                " ELSE " +
                " '未知' " +
                " END " +
                " ) as '性别', b.customer_age as '年龄',a.recharge_time as '时间'," +
                " ifnull(ifnull(a.tc_name,c.tc_name),'') as '现场TC', a.recharge_people as '收费人', b.customer_status as '客户状态', ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " a.remarks as  '备注' from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " a left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " b on a.customer_id=b.id" +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " c on a.deal_id=c.id " +
                " where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id order by a.recharge_time desc) t union all  " +
                " select  '合计' as '客户姓名','' as '电话','' as '性别','' as '年龄','' as  '时间'," +
                " '' as '现场TC', '' as '收费人',sum(t.actualAccountBalance) AS '客户状态',";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " sum(t." + list1.get(i).getCheckoutMethod() + ") ,";
            }
        }
        sql += " '' as  '备注' from (select '合计' as '客户姓名','' as '电话','' as '性别','' as '年龄','' as  '时间',  " +
                " '' as '现场TC','' as '收费人'," +
                " a.actual_account_balance as actualAccountBalance, ";
        if (list1.size() > 0) {
            for (int i = 0; i < list1.size(); i++) {
                sql += " (case when a.checkout_method='" + list1.get(i).getCheckoutMethod() + "' then a.actual_account_balance else 0 end) as '" + list1.get(i).getCheckoutMethod() + "',";
            }
        }
        sql += " '' as  '备注' from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " a left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " b on a.customer_id=b.id  " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " c on a.deal_id=c.id where 1=1 and a.isEarnes!=3 and !(a.isEarnes=2 and a.section_performance=0 and a.hospital_performance=0)";
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            sql += "  and (b.customer_name like CONCAT('%','" + reach + "','%') or b.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " b.wechat_number like CONCAT('%','" + reach + "','%') or b.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or b.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getCustomerStatus())) {
            sql += " and b.customer_status = '" + searchPaymentTotalInfo.getCustomerStatus() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getFieldTc())) {
            sql += " and (c.tc_name = '" + searchPaymentTotalInfo.getFieldTc() + "' or a.tc_name ='"+searchPaymentTotalInfo.getFieldTc()+"')";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getDoctor())) {
            sql += " and b.doctor = '" + searchPaymentTotalInfo.getDoctor() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getStartTime())) {
            sql += " and a.recharge_time >= '" + searchPaymentTotalInfo.getStartTime() + "'";
        }
        if (StringUtil.isNotEmpty(searchPaymentTotalInfo.getEndTime())) {
            sql += " and a.recharge_time <= '" + searchPaymentTotalInfo.getEndTime() + "'";
        }
        sql += " group by a.id ) t";
        List<LinkedHashMap<String, Object>> list = sqlMapper.selectListLink(sql);
        return list;
    }
}